

df=sqlContext.read.orc("/user/hive/warehouse/dw.db/sms_log")
df.registerTempTable("base")
aaa=sqlContext.sql("""
    create table ganhuo.gaanhuo_20181107 as
    select
        *
    FROM
        base
    where msg_content LIKE '%红菇%'
      or msg_content LIKE '%方家铺子%'
      or msg_content LIKE '%盛耳%'
      or msg_content LIKE '%羊肚菌%'
      or msg_content LIKE '%海鲜干货%'
      or msg_content LIKE '%南北干货%'
      or msg_content LIKE '%滋补%'
      or msg_content LIKE '%佰旺林%'
      or msg_content LIKE '%绿帝%'
      or msg_content LIKE '%百瑞源%'
      or msg_content LIKE '%龙汪人家%'
      or msg_content LIKE '%富昌%'
      or msg_content LIKE '%北大荒绿野%'
      or msg_content LIKE '%金唐%'
      or msg_content LIKE '%禾煜%'
""")


aaa=sqlContext.sql("""
    select
        msisdn,
    smslabel  ,
    msg_content,
    cust_id ,
    insert_time,
    msg_report ,
    status ,
    provider_id ,
    city ,
    prov_id ,
    area
    FROM
        ganhuo.gaanhuo_20181107

""")
aaa.repartition(1).write.mode("overwrite").txt("/user/hive/warehouse/ganhuo.db/ganhuo_20181108/" )

aaa=sqlContext.sql("""
    INSERT INTO  table ganhuo.ganhuo_20181108 as
    select
        msisdn,
    smslabel  ,
    msg_content,
    cust_id ,
    insert_time,
    msg_report ,
    status ,
    provider_id ,
    city ,
    prov_id ,
    area,
    pt
    FROM
        ganhuo.gaanhuo_20181107

""")

aaa=sqlContext.sql("""
    create  table dw.bixuan as
    select
        *
    FROM
        dw.sms_log
    WHERE msg_content LIKE '%碧选%'
    or msg_content LIKE '%BIXUAN%'
    or msg_content LIKE '%bixuan%'
""")

import pandas as pd
aaa=sqlContext.sql("""
select * FROM ganhuo.gaanhuo_20181107
""")
writer = pd.ExcelWriter("/home/games_data/result/20180907/ganhuo.xlsx",engine='xlsxwriter')
aaa.toPandas().to_excel(writer, sheet_name='Sheet1')
writer.save()


# load data local inpath '/home/data/zeng/2018105.txt.txt' into table history.meirong partition(pt='20181105');



